package org.jsoft.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.jsoft.bean.MingxiBean;
import org.jsoft.bean.UserBean;
import org.jsoft.comm.Getcon;

public class UserDao {
	// 网银登陆密码，用户名和卡号都可登陆，首先判断用户输入的用户名或卡号时候存在，存在的话判断密码是否正确
	public int login(String logname, String pass) {
		Connection con = null;
		UserBean ub = new UserBean();
		PreparedStatement pst1 = null;
		PreparedStatement pst2 = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		int i = 0;// 如果最后i值返回为0，则说明用户输入的用户名或卡号不存在
		// 对用户输入的登陆名进行两个sql语句查询，如果用户输入的是网银用户名，则执行与sql1有关的语句
		// 如果用户输入的是卡号，则执行与sql2有关的语句
		String sql1 = "select * from user where webname=?";
		String sql2 = "select * from user where cardid=?";
		try {
			con = Getcon.getcon();
			pst1 = con.prepareStatement(sql1);
			pst1.setString(1, logname);
			pst2 = con.prepareStatement(sql2);
			pst2.setString(1, logname);
			rs1 = pst1.executeQuery();
			rs2 = pst2.executeQuery();
			if (rs1.next()) { // 如果用户输入的是网银用户名且存在，则查询当前用户名的所对应的登陆密码webpass
				i = 1; // 如果最终i的值为1，则说明用户输入的用户名，但是密码输入错误
				ub.setWebpass(rs1.getString("webpass"));
				ub.setCardid(rs1.getString("cardid"));
				ub.setName(rs1.getString("name"));
				if (pass.equals(ub.getWebpass())) {
					i = 2; // 如果最后i的返回值为2，则用户名和密码匹配正确
				}
			} else if (rs2.next()) { // 如果用户输入的是卡号且卡号存在，则进行以下操作
				i = 3;
				ub.setWebpass(rs2.getString("webpass"));
				ub.setName(rs2.getString("name"));
				ub.setCardid(rs2.getString("cardid"));
				if (pass.equals(ub.getWebpass())) {
					i = 4;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// Getcon.close(con, pst1, rs1);
			// Getcon.close(null, pst2, rs2);
		}
		return i;
	}
	public UserBean login0(String logname, String pass){
		Connection con = null;
		UserBean ub = new UserBean();
		UserBean ub1 = new UserBean();
		PreparedStatement pst1 = null;
		PreparedStatement pst2 = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		// 对用户输入的登陆名进行两个sql语句查询，如果用户输入的是网银用户名，则执行与sql1有关的语句
		// 如果用户输入的是卡号，则执行与sql2有关的语句
		String sql1 = "select * from user where webname=?";
		String sql2 = "select * from user where cardid=?";
		try {
			con = Getcon.getcon();
			pst1 = con.prepareStatement(sql1);
			pst1.setString(1, logname);
			pst2 = con.prepareStatement(sql2);
			pst2.setString(1, logname);
			rs1 = pst1.executeQuery();
			rs2 = pst2.executeQuery();
			if (rs1.next()) { // 如果用户输入的是网银用户名且存在，则查询当前用户名的所对应的登陆密码webpass
				ub.setWebpass(rs1.getString("webpass"));
				if (pass.equals(ub.getWebpass())) {
					ub1.setCardid(rs1.getString("cardid"));
					ub1.setName(rs1.getString("name"));
					ub1.setWebpass(rs1.getString("webpass"));
					ub1.setBalance(rs1.getDouble("balance"));
					ub1.setWelcome(rs1.getString("welcome"));
					ub1.setWebid(rs1.getString("webid"));
					ub1.setPhone(rs1.getString("phone"));
				}
			} else if (rs2.next()) { // 如果用户输入的是卡号且卡号存在，则进行以下操作
				ub.setWebpass(rs2.getString("webpass"));
				if (pass.equals(ub.getWebpass())) {
					ub1.setCardid(rs2.getString("cardid"));
					ub1.setName(rs2.getString("name"));
					ub1.setWebpass(rs2.getString("webpass"));
					ub1.setBalance(rs2.getDouble("balance"));
					ub1.setWelcome(rs2.getString("welcome"));
					ub1.setWebid(rs2.getString("webid"));
					ub1.setPhone(rs2.getString("phone"));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// Getcon.close(con, pst1, rs1);
			// Getcon.close(null, pst2, rs2);
		}
		return ub1;
	}
	//注册时直接登录获取用户信息的方法
	public UserBean register_login(String cardid){
		Connection con = null;
		UserBean ub = new UserBean();
		UserBean ub1 = new UserBean();
		PreparedStatement pst1 = null;
		PreparedStatement pst2 = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		// 对用户输入的登陆名进行两个sql语句查询，如果用户输入的是网银用户名，则执行与sql1有关的语句
		// 如果用户输入的是卡号，则执行与sql2有关的语句
		String sql1 = "select * from user where cardid=?";
		try {
			con = Getcon.getcon();
			pst1 = con.prepareStatement(sql1);
			pst1.setString(1, cardid);
			rs1 = pst1.executeQuery();
			if (rs1.next()) { // 如果用户输入的是网银用户名且存在，则查询当前用户名的所对应的登陆密码webpass
					ub1.setCardid(rs1.getString("cardid"));
					ub1.setName(rs1.getString("name"));
					ub1.setWebpass(rs1.getString("webpass"));
					ub1.setBalance(rs1.getDouble("balance"));
					ub1.setWelcome(rs1.getString("welcome"));
					ub1.setWebid(rs1.getString("webid"));
					ub1.setPhone(rs1.getString("phone"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// Getcon.close(con, pst1, rs1);
			// Getcon.close(null, pst2, rs2);
		}
		return ub1;
	}
	public UserBean transfer0(String tcardid){
		String sql="select * from user where cardid=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		UserBean ub = new UserBean();
		con = Getcon.getcon();
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, tcardid);
			rs = pst.executeQuery();
			while(rs.next()){
				ub.setName(rs.getString("name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ub;
	}
	// 转账功能,第一个参数为当前卡号，第二个参数为要转账的卡号，第三个参数为要转账的金额
	public int transfer(String cardid, String tcardid, double money) {
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		UserBean ub = new UserBean();
		int i = 0;  //如果最后i的返回值为0，则说明输入的对方卡号不存在
		// 查询当前卡号对应的卡号，余额
		String sql1 = "select * from user where cardid=?";
		try {
			con = Getcon.getcon();
			pst = con.prepareStatement(sql1);
			pst.setString(1, cardid);
			rs = pst.executeQuery();
			while (rs.next()) { // 将当前卡号，余额，状态三个属性set给userBean对象
				ub.setCardid(rs.getString("cardid"));
				ub.setBalance(rs.getDouble("balance"));
				ub.setStatus(rs.getInt("status"));
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		//查询对方卡号
		String sql = "select * from user where cardid=?";
		UserBean ub1 = new UserBean();
		try {
			con = Getcon.getcon();
			pst = con.prepareStatement(sql);
			pst.setString(1, tcardid);
			rs = pst.executeQuery();
			if (rs.next()) { // 如果对方卡存在，则获取对方卡的余额和卡的状态
				i=1;  //如果最后i的值为1，则说明对方卡号存在
				ub1.setBalance(rs.getDouble("balance"));
				ub1.setStatus(rs.getInt("status"));
				int str1 = ub1.getStatus();
				if (str1 == 1) {// 如果对方卡状态为1，即没被冻结，则执行以下操作
					i=2;  //如果最后i的返回值为2，则对方卡号没被冻结，可正常使用
					double charge = money * 0.005; // money为转账金额，charge为手续费
					double pay = money + charge;// pay为本金和手续费的金额
					if (pay <= ub.getBalance()) {// 如果pay小于本卡余额，则可以转账
						i=3;   //如果最后i的返回值为3，则说明转账成功
						sql = "update user set balance=balance-'" + pay
								+ "' where cardid='" + ub.getCardid() + "'"; // 执行本卡号余额减少操作
						ub.setBalance(ub.getBalance() - pay);// 该用户当前的余额
						pst = con.prepareStatement(sql);
						pst.execute();
						sql = "update user set balance=balance+'" + money
								+ "' where cardid='" + tcardid + "'"; // 执行对方卡号余额增加操作
						ub1.setBalance(ub1.getBalance()+money);
						pst = con.prepareStatement(sql);
						pst.execute();

						// 以下为向mingxi表中插入数据的执行代码
						Date dt = new Date();
						SimpleDateFormat d = new SimpleDateFormat("yyyy-MM-dd");
						String date = d.format(dt);
						//1、支出
						String sql0 = "insert into mingxi(mdate,mout,mcardid,mbalance) values(?,?,?,?)";
						pst = con.prepareStatement(sql0);
						pst.setString(1, date);
						pst.setDouble(2, pay);
						pst.setString(3, cardid);
						pst.setDouble(4, ub.getBalance());
						pst.executeUpdate();
						//2、收入
						String sqlt = "insert into mingxi(mdate,min,mcardid,mbalance) values(?,?,?,?)";
						pst = con.prepareStatement(sqlt);
						pst.setString(1, date);
						pst.setDouble(2, money);
						pst.setString(3, tcardid);
						pst.setDouble(4, ub1.getBalance());
						pst.executeUpdate();
					}
				}
			}
			if (tcardid.equals(cardid)) {// 如果输入的对方的卡号和本卡号相同，则提示不可以向本卡转账
				i=4;//输入的对方卡号为本卡卡号
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			Getcon.close(con, pst, rs);
		}
		return i;
	}

	// 查询状态，包括卡的状态和余额
	public UserBean selectStatus(String cardid) {
		String sql = "select webid,status,balance from user where cardid=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = Getcon.getcon();
		UserBean ub = new UserBean();
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, cardid);
			rs = pst.executeQuery();
			while (rs.next()) {
				ub.setWebid(rs.getString("webid"));
				ub.setStatus(rs.getInt("status"));
				ub.setBalance(rs.getDouble("balance"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Getcon.close(con, pst, rs);
		}
		return ub;
	}
	//查询用户信息的方法
	public UserBean selectuser(String cardid) {
		String sql = "select * from user where cardid=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = Getcon.getcon();
		UserBean ub = new UserBean();
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, cardid);
			rs = pst.executeQuery();
			while (rs.next()) {
				ub.setWebid(rs.getString("webid"));
				ub.setWebname(rs.getString("webname"));
				ub.setName(rs.getString("name"));
				ub.setUserid(rs.getString("userid"));
				ub.setCardid(rs.getString("cardid"));
				ub.setWebpass(rs.getString("webpass"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ub;
	}

	public int zhaohui1(String cardid, String userid, String pass) {
		String sql = "select * from user where cardid=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = Getcon.getcon();
		int i = 0; // 如果最终i=0,则说明用户银行卡号直接不存在
		UserBean ub = new UserBean();
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, cardid);
			rs = pst.executeQuery();
			if (rs.next()) {
					i = 1;// 如果i等于1，则说明该用户存在，但是用户身份证号不正确
					ub.setCardid(rs.getString("cardid"));
					ub.setUserid(rs.getString("userid"));
					ub.setPass(rs.getString("pass"));
					if (userid.equals(ub.getUserid())) {
						i = 2; // 如果i等于2，则说明用户存在，且身份证号正确
						if (pass.equals(ub.getPass())) {
							i = 3;// 如果i等于3，则用户存在，身份证号正确，密码正确
						}
					}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Getcon.close(con, pst, rs);
		}
		return i;
	}
	// 注册方法第一步，输入银行卡号、身份证号和密码
	public int register1(String cardid, String userid, String pass) {
		String sql = "select * from user where cardid=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = Getcon.getcon();
		int i = 0; // 如果最终i=0,则说明用户银行卡号直接不存在
		UserBean ub = new UserBean();
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, cardid);
			rs = pst.executeQuery();
			if (rs.next()) {
				String webid=rs.getString("webid");
				if(webid!=null){
					i=-1;
				}else{
				i = 1;// 如果i等于1，则说明该用户存在，但是用户身份证号不正确
				ub.setCardid(rs.getString("cardid"));
				ub.setUserid(rs.getString("userid"));
				ub.setPass(rs.getString("pass"));
				if (userid.equals(ub.getUserid())) {
					i = 2; // 如果i等于2，则说明用户存在，且身份证号正确
					if (pass.equals(ub.getPass())) {
						i = 3;// 如果i等于3，则用户存在，身份证号正确，密码正确
					}
				}
			}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Getcon.close(con, pst, rs);
		}
		return i;
	}

	// 注册方法的第二部，用户输入网银用户名和密码
	public int register2(String cardid, String webname, String webpass1) {
		String sql = "update user set webname=?,webpass=?,webid=? where cardid=?";
		int i = 0;
		Connection con = null;
		PreparedStatement pst = null;
		con = Getcon.getcon();
		int d = (int) (Math.random() * 100000);
		String webid = String.valueOf(d);
		try {
				pst = con.prepareStatement(sql);
				i = 1; // 如果最后i的返回值为1，则说明用户输入的两次密码相同
				pst.setString(1, webname);
				pst.setString(2, webpass1);
				pst.setString(3, webid);
				pst.setString(4, cardid);
				pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// Getcon.close(con, pst, rs);
		}
		return i;
	}
	//修改密码方法
	public int changepass(String cardid, String webpass, String webpass1,
			String webpass2) {
		String sql = "select webpass from user where cardid=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		UserBean ub = new UserBean();
		con = Getcon.getcon();
		int i = 0; // 如果最后i的返回值为0，则说明原始密码输入错误
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, cardid);
			rs = pst.executeQuery();
			if (rs.next()) {
				ub.setWebpass(rs.getString("webpass"));
				if (webpass.equals(ub.getWebpass())) {
					i = 1; // 如果最后i的返回值为1，则说明原始密码输入正确，但是新密码两次不对应
					if (webpass1.equals(webpass2)) {
						i = 2; // 如果最后i的返回值为2，则原始密码正确，且两次密码输入相同
						String sql1 = "update user set webpass=? where cardid=?";
						pst = con.prepareStatement(sql1);
						pst.setString(1, webpass1);
						pst.setString(2, cardid);
						pst.executeUpdate();
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	//查询明细方法
	public List<MingxiBean> selectmingxi(String cardid){
		String sql="select * from mingxi where mcardid=?";
		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		con=Getcon.getcon();
		List<MingxiBean> list=new ArrayList<MingxiBean>();
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, cardid);
			rs=pst.executeQuery();
			while(rs.next()){
				MingxiBean mxb=new MingxiBean();
				mxb.setDate(rs.getString("mdate"));
				mxb.setId(rs.getInt("id"));
				mxb.setIn(rs.getDouble("min"));
				mxb.setMbalance(rs.getDouble("mbalance"));
				mxb.setMcardid(rs.getString("mcardid"));
				mxb.setOut(rs.getDouble("mout"));
				list.add(mxb);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			Getcon.close(con, pst, rs);
		}
		return list;
	}
	//找回密码修改密码时的修改密码
	public void zhaohui_pass(String cardid,String webpass){
		String sql="update user set webpass=? where cardid=?";
		PreparedStatement pst=null;
		Connection con=null;
		con=Getcon.getcon();
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, webpass);
			pst.setString(2, cardid);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	//往数据库里插入欢迎信息的方法
	public void welcome_dao(String cardid,String welcome){
		String sql="update user set welcome=? where cardid=?";
		PreparedStatement pst=null;
		Connection con=null;
		try {
			con=Getcon.getcon();
			pst=con.prepareStatement(sql);
			pst.setString(1, welcome);
			pst.setString(2, cardid);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//修改用户信息的方法
	public int update_user(String cardid,String phone,String webname){
		String sql="update user set phone=?,webname=? where cardid=?";
		PreparedStatement pst=null;
		Connection con=null;
		int i=0;
		con=Getcon.getcon();
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, phone);
			pst.setString(2, webname);
			pst.setString(3, cardid);
			i=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	public void error_time(String cardid,String etime){
		String sql="update user set etime=? where cardid=?";
		PreparedStatement pst=null;
		Connection con=null;
		con=Getcon.getcon();
		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, etime);
			pst.setString(2, cardid);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
